--/
CREATE PROCEDURE sp_bomDrillDown3
           @bomCode nvarchar(50),
         @bomVersion nvarchar(50)
AS

create table #bt_bom_bomTree_view(
        bomlevel int,
        code varchar(50),
        bomCode nvarchar(50),
        version nvarchar(50),
        pCode nvarchar(50),
        bomPCode varchar(50),
        pVersion nvarchar(50),
        materielCode nvarchar(50),
        materielName nvarchar(50),
        effectiveDate varchar(20),
        expiryDate varchar(20),
        quantityPer varchar(20),
        fixedLoss varchar(20),
        changeLossRate varchar(20),
        unitNumTypeCode varchar(50),
	unitNumTypeName varchar(100),
	materielTypeName varchar(100), 
	materielStandard varchar(100),
	manuLossTypeName varchar(100),
	issuedWayName varchar(100),
	issuedProcessName varchar(100),
	issuedWareName varchar(100),
	issuedGoodsName varchar(100),
	auditStatus varchar(10),--审核状态
        flag int 
)
declare @level int  
set @level =1
declare @isBom int 
declare @routingCode varchar(50) 
select @isBom =s.countNum from( select count(0) as countNum  from b_bomTop_info2 where bomCode = @bomCode and bomVersion = @bomVersion and status='1') s
print @isBom
IF(@isBom>0)
        begin
                insert into #bt_bom_bomTree_view select @level,s.bomCode,s.bomCode+'_'+s.bomVersion,s.bomVersion as version,'0' as pCode,'' as bomPCode,'' as 'pVersion',s.materielCode,
				s1.materielName,s.effectiveDate,s.expiryDate,s.quantityPer,s.fixedLoss,s.changeLossRate,'',
				'' as 'unitNumTypeName',
				(select exp.expTabName from s_expandTable_info exp where  exp.expTabCode = s1.materielTypeCode) as 'materielTypeName',
				s1.materielStandard,'' as 'manuLossTypeName',
				'' as 'issuedWayName',
				'' as 'issuedProcessName',
				'' as 'issuedWareName',
				'' as 'issuedGoodsName',case s.auditStatus when '1' then '已审核' else '未审核' end as 'auditStatus',1 as flag 
                from(select bomCode,bomVersion,materielCode,materielName,convert(varchar(100),effectiveDate,23) as 'effectiveDate',convert(varchar(100),expiryDate,23) as 'expiryDate',auditStatus,
				1 as 'quantityPer' ,'' as 'fixedLoss','' as 'changeLossRate' from b_bomTop_info2 where status='1' and bomCode = @bomCode and bomVersion = @bomVersion) s 
				left join b_materiel_info1 s1 on s.materielCode = s1.materielCode where s1.status = '1' 
				
		select @routingCode=processCode from b_bomTop_info2 where status = '1'  and bomCode = @bomCode and bomVersion = @bomVersion
        end     
        while exists (select * from #bt_bom_bomTree_view where  #bt_bom_bomTree_view.flag !=0)
       
        begin 
                if exists (select * from  #bt_bom_bomTree_view  where bomlevel = @level and flag!=0)
                 
                        begin
                                set rowcount 1
                                insert into #bt_bom_bomTree_view select  @level+1,s.bomCode,s.bomCode+'_'+s.version,s.version,s.pCode+'_'+s.pVersion,s.pCode,s.pVersion,s.materielCode,s.materielName,
								s.effectiveDate,s.expiryDate,s.quantityPer,s.fixedLoss,s.changeLossRate,s.unitNumTypeCode,
								(select exp.expTabName from s_expandTable_info exp where  exp.expTabCode = s.unitNumTypeCode) as 'unitNumTypeName',
								(select exp.expTabName from s_expandTable_info exp where  exp.expTabCode = s.materielTypeCode) as 'materielTypeName',
								s.materielStandard,(select exp.expTabName from s_expandTable_info exp where  exp.expTabCode = s.manuLossTypeCode) as 'manuLossTypeName',
								(select exp.expTabName from s_expandTable_info exp where  exp.expTabCode = s.issuedWayCode) as 'issuedWayName',
								s.issuedProcessCode as 'issuedProcessName',
								(select name from s_warehouse_info where status = '1' and wareId = s.issuedWareCode) as 'issuedWareName',
								(select name from s_wareGoodsAllocation_info where status = '1' and  GoodsAllocationId = s.issuedGoodsCode) as 'issuedGoodsName',case s.auditStatus when '1' then '已审核' else '未审核' end as 'auditStatus',
								1 as flag from 
								(select b.bomCode,b.version,b.pCode,b.pVersion,b.materielCode,b1.materielName,
								convert(varchar(100),b.effectiveDate,23) as 'effectiveDate',convert(varchar(100),b.expiryDate,23) as 'expiryDate' ,b.quantityPer,b2.fixedLoss,b2.changeLossRate,b.unitNumTypeCode,b.auditStatus,
								b1.materielStandard,b1.materielTypeCode,b.manuLossTypeCode,b.issuedWayCode,b4.processCode as 'issuedProcessCode',b3.wareCode as 'issuedWareCode',b3.GoodsAllocationCode as 'issuedGoodsCode'   from 
								(select *  from b_bom_info2  where status = '1' ) b left join (select * from  b_materiel_info1 where  status ='1' ) b1 
								on b.materielCode = b1.materielCode left join (select materielCode , changeLossRate, fixedLoss from br_materiel_manufacture1 where status ='1' )b2 on b.materielCode = b2.materielCode 
								left join ( select materielCode ,wareCode ,GoodsAllocationCode from br_materiel_stock1 where status ='1' )b3 on b.materielCode = b3.materielCode  left join (select materielCode,processCode from b_process_info where  status = '1' and routingCode = @routingCode ) b4 on b.materielCode = b4.materielCode) s 
                                inner join #bt_bom_bomTree_view on #bt_bom_bomTree_view.bomCode = s.pCode+'_'+s.pVersion
                                where  #bt_bom_bomTree_view.bomlevel =@level 
                                and s.bomCode+'_'+s.version not in (select #bt_bom_bomTree_view.bomCode  from #bt_bom_bomTree_view where bomlevel=@level )and flag!=0                               
                                set rowcount 0
                                print 'child node'
                                set @level =@level +1
                                print @level
                        end 
                else if exists(
                        select s.pCode+'_'+s.pVersion from b_bom_info2 s  inner join #bt_bom_bomTree_view on #bt_bom_bomTree_view.bomCode = s.pCode+'_'+s.pVersion
                        where #bt_bom_bomTree_view.bomlevel=@level-2 and s.status = '1' 
                        and s.bomCode+'_'+s.version not in (select #bt_bom_bomTree_view.bomCode from #bt_bom_bomTree_view where bomlevel=@level-1 ))
                         
                        begin
                                set rowcount 1
                                insert into #bt_bom_bomTree_view select @level-1,s.bomCode,s.bomCode+'_'+s.version,s.version,s.pCode+'_'+s.pVersion,s.pCode,s.pVersion,s.materielCode,s.materielName,								
								s.effectiveDate,s.expiryDate,s.quantityPer,s.fixedLoss,s.changeLossRate,s.unitNumTypeCode,
								(select exp.expTabName from s_expandTable_info exp where  exp.expTabCode = s.unitNumTypeCode) as 'unitNumTypeName',
								(select exp.expTabName from s_expandTable_info exp where  exp.expTabCode = s.materielTypeCode) as 'materielTypeName',
								s.materielStandard,(select exp.expTabName from s_expandTable_info exp where  exp.expTabCode = s.manuLossTypeCode) as 'manuLossTypeName',
								(select exp.expTabName from s_expandTable_info exp where  exp.expTabCode = s.issuedWayCode) as 'issuedWayName',
								s.issuedProcessCode as 'issuedProcessName',
								(select name from s_warehouse_info where status = '1' and wareId = s.issuedWareCode) as 'issuedWareName',
								(select name from s_wareGoodsAllocation_info where status = '1' and  GoodsAllocationId = s.issuedGoodsCode) as 'issuedGoodsName',case s.auditStatus when '1' then '已审核' else '未审核' end as 'auditStatus',
								1 as flag from ( select b.bomCode,b.version,b.pCode,b.pVersion,b.materielCode,b.materielName,convert(varchar(100),b.effectiveDate,23) as 'effectiveDate',
								convert(varchar(100),b.expiryDate,23) as 'expiryDate',b.quantityPer,b2.fixedLoss,b2.changeLossRate,b.unitNumTypeCode,b.auditStatus,
								b1.materielStandard,b1.materielTypeCode,b.manuLossTypeCode,b.issuedWayCode,b4.processCode as 'issuedProcessCode',b3.wareCode as 'issuedWareCode',b3.GoodsAllocationCode as 'issuedGoodsCode'   from 
								(select *  from b_bom_info2  where status = '1' ) b left join (select * from  b_materiel_info1 where  status ='1' ) b1 
								on b.materielCode = b1.materielCode left join (select materielCode , changeLossRate, fixedLoss from br_materiel_manufacture1 where status ='1' )b2 on b.materielCode = b2.materielCode 
								left join ( select materielCode ,wareCode ,GoodsAllocationCode from br_materiel_stock1 where status ='1' )b3 on b.materielCode = b3.materielCode  left join (select materielCode,processCode from b_process_info where  status = '1' and routingCode = @routingCode ) b4 on b.materielCode = b4.materielCode) s  
                                inner join #bt_bom_bomTree_view on #bt_bom_bomTree_view.bomCode = s.pCode+'_'+s.pVersion
                                where #bt_bom_bomTree_view.bomlevel =@level -2  and s.bomCode+'_'+s.version not in (select #bt_bom_bomTree_view.bomCode from #bt_bom_bomTree_view where bomlevel=@level-1 )
                               
                                set @level =@level -1
                                set rowcount 0
                                print 'brother node'
                        end
                else 
                        begin
                                update #bt_bom_bomTree_view set flag =0 where bomlevel =@level-1 and flag !=0  
                                
                                update #bt_bom_bomTree_view set flag =0 where bomlevel =@level-2 and flag !=0  
                                print 'return'
                                
                                set @level =@level -1
                        end 
        end
        select * from #bt_bom_bomTree_view
/
